let mysql = require("mysql");
let pool = mysql.createPool({
  connectionLimit: 300,
  host: "db.chenshengkai.top",
  user: "chenshengkai",
  password: "chenshengkai1.0",
  database: "country_garden",
});
// 包装执行 sql 的方法
async function executeSql(connection, sql) {
  return new Promise(async (resolve, reject) => {
    connection.query(sql, (error, result) => {
      if (error) {
        return reject(error);
      }
      return resolve(result);
    });
  });
}
// 使用事务执行 sql 方法
async function transaction(func) {
  return new Promise((resolve, reject) => {
    pool.getConnection(async (error, connection) => {
      try {
        await connection.beginTransaction();
        let result = await func(connection);
        await connection.commit();
        return resolve(result);
      } catch (error) {
        await connection.rollback();
        return reject(error);
      } finally {
        await connection.release();
      }
    });
  });
}
// 获取总数的模版
async function getTotal(connection, tableName, orderName, where) {
  let sql = `select count(*) as total from ${tableName} ${where} order by ${orderName}`;
  let result = await executeSql(connection, sql);
  return result[0].total;
}
// 获取分页的模版
async function getByPagination(
  connection,
  tableName,
  orderName,
  where,
  pageSize,
  currentPage,
) {
  let sql = "";
  if (orderName !== "opDatetime") {
    orderName = `${orderName}, opDatetime`;
  }
  if (
    tableName === "TaskTable" ||
    tableName === "PayTable" ||
    tableName === "NoteTable"
  ) {
    sql = `select *, DATE_FORMAT(opDatetime, '%Y-%m-%d %H:%i:%s') as opDatetime, DATE_FORMAT(sendDatetime, '%Y-%m-%d %H:%i:%s') as sendDatetime from ${tableName} ${where} order by ${orderName} limit ${pageSize} offset ${(currentPage - 1) * pageSize}`;
  } else {
    sql = `select *, DATE_FORMAT(opDatetime, '%Y-%m-%d %H:%i:%s') as opDatetime from ${tableName} ${where} order by ${orderName} limit ${pageSize} offset ${(currentPage - 1) * pageSize}`;
  }
  return await executeSql(connection, sql);
}
// 通过 uuid 删除的模版
async function removeByUuid(connection, tableName, uuid) {
  let sql = `delete from ${tableName} where uuid='${uuid}'`;
  await executeSql(connection, sql);
}
// 获取全部模版
async function getAll(connection, tableName, orderName) {
  let pageSize = 100;
  let currentPage = 1;
  let result = [];
  let list = [];
  do {
    list = await getByPagination(
      connection,
      tableName,
      orderName,
      "",
      pageSize,
      currentPage,
    );
    result = [...result, ...list];
    currentPage = currentPage + 1;
  } while (list.length === pageSize);
  return result;
}
module.exports = {
  executeSql,
  transaction,
  getTotal,
  getByPagination,
  removeByUuid,
  getAll,
};
